package com.ihr360.excel.util;

import com.ihr360.excel.annotation.ExcelConfig;
import com.ihr360.excel.exception.ExcelException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.NumberToTextConverter;

import java.sql.Timestamp;
import java.text.ParseException;
import java.util.Date;
import java.util.Iterator;

public class CommonExcelUtil {

    private static final ThreadLocal<DataFormatter> dataFormatterThreadLocal = new ThreadLocal<>();


    /**
     * Cell是null 或是CellType.STRING类型的，但是值是blank的
     *
     * @param cell
     * @return
     */
    public static boolean isNullOrBlankStringCell(Cell cell) {
        return cell == null
                || cell.getCellType() == Cell.CELL_TYPE_BLANK
                || (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils.isBlank(cell.getStringCellValue()));
    }

    /**
     * 获取单元格值
     *
     * @param cell
     * @param excelConfig
     * @param specificationType
     * @return
     */
    public static Object getCellValue(Cell cell, ExcelConfig excelConfig, Class specificationType) throws ParseException {

        if (isNullOrBlankStringCell(cell)) {
            return null;
        }

        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_BLANK:
                return null;
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_ERROR:
                return cell.getErrorCellValue();
            case Cell.CELL_TYPE_FORMULA:
                //公式四舍五入保留两位
                try {
                    return new java.text.DecimalFormat("#.00").format(cell.getNumericCellValue());
                }catch (Exception e){
                    return "";
                }
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {// 判断是日期类型
                    if (specificationType == String.class) {
                        DataFormatter formatter = getDataFormatter();
                        return formatter.formatCellValue(cell);
                    }
                    return cell.getDateCellValue();
                } else {
                    // 解决问题：
                    // 1、科学计数法(如2.6E+10)，
                    // 2、超长小数小数位不一致（如1091.19649281798读取出1091.1964928179796），
                    // 3、整型变小数（如0读取出0.0）

                    String strValue = NumberToTextConverter.toText(cell.getNumericCellValue());

                    return getSpecificationType(specificationType, strValue);

                    /*double doubleVal = cell.getNumericCellValue();
                    long longVal = Math.round(cell.getNumericCellValue());
                    if (Double.parseDouble(longVal + ".0") == doubleVal) {
                        return longVal;
                    }
                    return doubleVal;*/
                }

            case Cell.CELL_TYPE_STRING:
                String cellValue = cell.getStringCellValue();
                if (StringUtils.isNotBlank(cellValue) && excelConfig != null && StringUtils.isNotEmpty(excelConfig.globalRemovePattern())) {
                    cellValue = cellValue.trim().replaceAll(excelConfig.globalRemovePattern(), "");
                } else if (StringUtils.isEmpty(cellValue)) {
                    return null;
                }
                return getSpecificationType(specificationType, cellValue);
            default:
                return null;
        }
    }

    public static boolean checkBlankRow(ExcelConfig excelConfig, Row row) {
        boolean allRowIsNull = true;
        if (row == null) {
            return allRowIsNull;
        }
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Object cellValue = null;
            try {
                cellValue = getCellValue(cellIterator.next(), excelConfig, null);
            } catch (ParseException e) {
                throw new ExcelException("验证空行时，发生数据转换异常！");
            }
            if (cellValue != null && StringUtils.isNotBlank(String.valueOf(cellValue))) {
                allRowIsNull = false;
                break;
            }
        }
        return allRowIsNull;
    }


    private static DataFormatter getDataFormatter() {
        DataFormatter formatter = dataFormatterThreadLocal.get();
        if (formatter == null) {
            formatter = new DataFormatter();
            dataFormatterThreadLocal.set(formatter);
        }
        return formatter;
    }

    private static Object getSpecificationType(Class specificationType, String cellValue) throws ParseException {

        if (specificationType == null) {
            return cellValue;
        }
        if (specificationType == Date.class) {
            return ExcelDateParser.getDate(cellValue);
        } else if (specificationType == Timestamp.class) {
            return ExcelDateParser.getTimestamp(cellValue);
        } else if (specificationType == Double.class) {
            return Double.parseDouble(cellValue);
        } else if (specificationType == Long.class) {
            return Long.parseLong(cellValue);
        } else if (specificationType == Integer.class) {
            return Integer.parseInt(cellValue);
        } else {
            return cellValue;
        }
    }


}
